package com.qdlc.p2p.dal.dao.impl;

import java.util.ArrayList;
import java.util.List;

import javax.annotation.Resource;
import javax.persistence.Query;

import org.springframework.cache.annotation.CachePut;
import org.springframework.cache.annotation.Cacheable;
import org.springframework.stereotype.Repository;

import com.qdlc.p2p.common.constant.Global;
import com.qdlc.p2p.common.constant.UserCacheStatus;
import com.qdlc.p2p.common.constant.UserNature;
import com.qdlc.p2p.common.exception.UserException;
import com.qdlc.p2p.common.util.Page;
import com.qdlc.p2p.common.util.StringUtil;
import com.qdlc.p2p.dal.common.dao.jpa.BaseDaoImpl;
import com.qdlc.p2p.dal.common.model.jpa.OrderFilter.OrderType;
import com.qdlc.p2p.dal.common.model.jpa.PageDataList;
import com.qdlc.p2p.dal.common.model.jpa.QueryParam;
import com.qdlc.p2p.dal.common.model.jpa.SearchFilter;
import com.qdlc.p2p.dal.common.model.jpa.SearchFilter.Operators;
import com.qdlc.p2p.dal.dao.UserCacheDao;
import com.qdlc.p2p.dal.dao.UserDao;
import com.qdlc.p2p.dal.dao.UserIdentifyDao;
import com.qdlc.p2p.dal.dto.User;
import com.qdlc.p2p.dal.dto.UserCache;
import com.qdlc.p2p.dal.dto.UserIdentify;
import com.qdlc.p2p.dal.model.BorrowModel;
import com.qdlc.p2p.dal.model.UserModel;
import com.qdlc.p2p.dal.util.UserUtils;

/**
 * @author linya 2016-02-15
 */
@Repository("userDao")
public class UserDaoImpl extends BaseDaoImpl<User> implements UserDao {

    @Resource
    private UserIdentifyDao userIdentifyDao;

    @Resource
    private UserCacheDao userCacheDao;

    public int count(QueryParam param) {
        return countByCriteria(param);
    }

    @Override
    public int countByUserName(String userName) {
        return countByCriteria(QueryParam.getInstance().addParam("userName", userName));
    }

    @Override
    public int countByCardId(String cardId) {
        return countByCriteria(QueryParam.getInstance().addParam("cardId", cardId));
    }

    @Override
    public int countByMobilePhone(String mobilePhone) {
        return countByCriteria(QueryParam.getInstance().addParam("mobilePhone", mobilePhone));
    }

    @Override
    public int countByEmail(String email) {
        return countByCriteria(QueryParam.getInstance().addParam("email", email));
    }

    @Override
    public int countByEmail(String email, long userId) {
        return countByCriteria(QueryParam.getInstance().addParam("email", email).addParam("userId", Operators.NOTEQ, userId));
    }


    @Override
    @CachePut(value = "user", key = "#user.userId")
    public User update(User user) {
        return super.update(user);
    }

    @Override
    @CachePut(value = "user", key = "#user.userId")
    public User modifyPwd(User user) {
        String sql = "UPDATE User SET pwd = :pwd WHERE userId = :userId";
        Query query = em.createQuery(sql);
        query.setParameter("pwd", user.getPwd());
        query.setParameter("userId", user.getUserId());
        int result = query.executeUpdate();
        if (result != 1) {
            throw new UserException("密码修改失败！");
        }
        return user;
    }

    @Override
    @CachePut(value = "user", key = "#user.userId")
    public User modifyPaypwd(User user) {
        String sql = "UPDATE User SET payPwd = :payPwd WHERE userId = :userId";
        Query query = em.createQuery(sql);
        query.setParameter("payPwd", user.getPayPwd());
        query.setParameter("userId", user.getUserId());
        int result = query.executeUpdate();
        if (result != 1) {
            throw new UserException("交易密码修改失败！");
        }
        return user;
    }

    @Override
    @CachePut(value = "user", key = "#user.userId")
    public User modifyEmail(User user, String email) {
        String sql = "UPDATE User SET email = :email WHERE userId = :userId";
        Query query = em.createQuery(sql);
        query.setParameter("email", email);
        query.setParameter("userId", user.getUserId());
        int result = query.executeUpdate();
        if (result != 1) {
            throw new UserException("更新邮箱认证失败！", 1);
        }
        //设置新邮箱到User对象
        user.setEmail(email);
        return user;
    }

    @Override
    @Cacheable(value = "user", key = "#userName")
    public User getUserByUserName(String userName) {
        User user = super.findObjByProperty("userName", userName);
        if (user != null) {
            return user;
        }
        return null;
    }

    @Override
    @Cacheable(value = "user", key = "#userId")
    public User getUserById(long userId) {
        return super.find(userId);
    }

    @Override
    @CachePut(value = "user", key = "#user.userId")
    public User modifyPhone(User user, String mobilePhone) {
        String sql = "UPDATE User SET mobilePhone = :mobilePhone WHERE userId = :userId";
        Query query = em.createQuery(sql);
        query.setParameter("mobilePhone", mobilePhone);
        query.setParameter("userId", user.getUserId());
        int result = query.executeUpdate();
        if (result != 1) {
            throw new UserException("更新手机认证失败！", 1);
        }
        user.setMobilePhone(mobilePhone);
        return user;
    }

    @Override
    public PageDataList<UserModel> userList(int pageNumber, int pageSize, UserModel model) {
        QueryParam param = QueryParam.getInstance().addPage(pageNumber, pageSize);
        param.addOrder(OrderType.DESC, "u.user_id");
        if (!StringUtil.isBlank(model.getSearchName())) {
            SearchFilter orFilter1 = new SearchFilter("user_name", Operators.LIKE, model.getSearchName().trim());
            SearchFilter orFilter2 = new SearchFilter("real_name", Operators.LIKE, model.getSearchName().trim());
            param.addOrFilter(orFilter1, orFilter2);
        } else {
            UserCache userCache = UserUtils.getUserCacheByUserId(model.getUserId());
            if (userCache != null && userCache.getUserNature() != 0) {
                param.addParam("user_nature", userCache.getUserNature());
            }
            if (model.getStatus() != 99) {
                param.addParam("status", model.getStatus());
            }
            if (model.getUserIdentify() != null && model.getUserIdentify().getRealNameStatus() != -2) {
                param.addParam("real_name_status", model.getUserIdentify().getRealNameStatus());
            }
            if (!StringUtil.isBlank(model.getUserName())) {
                param.addParam("user_name", Operators.LIKE, model.getUserName().trim());
            }
            if (!StringUtil.isBlank(model.getRealName())) {
                param.addParam("real_name", Operators.LIKE, model.getRealName().trim());
            }

            if (!StringUtil.isBlank(model.getMobilePhone())) {
                param.addParam("real_name", Operators.LIKE, model.getRealName().trim());
            }

            if (!StringUtil.isBlank(model.getMobilePhone())) {
                param.addParam("mobile_phone", Operators.LIKE, model.getMobilePhone().trim());
            }

            if (StringUtil.isNotBlank(model.getStartTime())) {
                String start = model.getStartTime() + " 00:00:00";
                param.addParam("add_time", Operators.GT, start);
            }
            if (StringUtil.isNotBlank(model.getEndTime())) {
                String end = model.getEndTime() + " 23:59:59";
                param.addParam("add_time", Operators.LTE, end);
            }
        }
        PageDataList<User> plist = this.findPageListBySql("FROM " + Global.DB_PREFIX + "user u LEFT JOIN " + Global.DB_PREFIX + "user_cache c ON c.user_id = u.user_id "
                + "LEFT JOIN " + Global.DB_PREFIX + "user_identify i ON i.user_id = u.user_id", param);
        PageDataList<UserModel> uList = new PageDataList<UserModel>();
        uList.setPage(plist.getPage());
        List<UserModel> list = new ArrayList<UserModel>();
        if (plist.getList().size() > 0) {
            for (int i = 0; i < plist.getList().size(); i++) {
                User user = (User) plist.getList().get(i);
                UserIdentify userIdentify = userIdentifyDao.findByUserId(user.getUserId());
                UserCache userCache = userCacheDao.findByUserId(user.getUserId());
                UserModel userModel = new UserModel();
                userModel.setUserId(user.getUserId());
                userModel.setUserName(user.getUserName());
                userModel.setRealName(user.getRealName());
                userModel.setEmail(user.getEmail());
                userModel.setMobilePhone(user.getMobilePhone());
                userModel.setCardId(user.getCardId());
                userModel.setAddTime(user.getAddTime());
                userModel.setRealNameStatus(userIdentify.getRealNameStatus());
                userModel.setSex(userCache.getSex());
                userModel.setUserNature(userCache.getUserNature());
                userModel.setUserType(userCache.getUserType());
                userModel.setStatus(userCache.getStatus());
                list.add(userModel);
            }
        }
        uList.setList(list);
        return uList;
    }

    @SuppressWarnings("unchecked")
    @Override
    public PageDataList<User> findUserList(BorrowModel model) {
        PageDataList<User> pageDataList = new PageDataList<User>();
        StringBuffer sb = new StringBuffer();
        if (model.getScales() == 101) {
            sb.append("select " + Global.DB_PREFIX + "user.* from " + Global.DB_PREFIX + "user," + Global.DB_PREFIX + "user_cache "
                    + "where " + Global.DB_PREFIX + "user.user_id=" + Global.DB_PREFIX + "user_cache.user_id "
                    + "and " + Global.DB_PREFIX + "user.api_status=1 and (" + Global.DB_PREFIX + "user_cache.user_type=2 or " + Global.DB_PREFIX + "user_cache.user_type=3)");
        } else {
            sb.append("select " + Global.DB_PREFIX + "user.* from " + Global.DB_PREFIX + "user," + Global.DB_PREFIX + "user_identify," + Global.DB_PREFIX + "user_cache "
                    + "where " + Global.DB_PREFIX + "user.user_id=" + Global.DB_PREFIX + "user_identify.user_id and " + Global.DB_PREFIX + "user.user_id=" + Global.DB_PREFIX + "user_cache.user_id "
                    + "and " + Global.DB_PREFIX + "user_identify.real_name_status=1 and " + Global.DB_PREFIX + "user_identify.mobile_phone_status=1 "
                    + "and (" + Global.DB_PREFIX + "user_cache.user_type=2 or " + Global.DB_PREFIX + "user_cache.user_type=3)");
        }
        if (!StringUtil.isBlank(model.getSearchName())) {
            sb.append(" and (" + Global.DB_PREFIX + "user.real_name like '%" + model.getSearchName().trim()
                    + "%' or " + Global.DB_PREFIX + "user.email like '%" + model.getSearchName()
                    + "%' or " + Global.DB_PREFIX + "user_cache.company_name like '%" + model.getSearchName()
                    + "%' or " + Global.DB_PREFIX + "user.user_name like '%" + model.getSearchName().trim() + "%' )");
        }
        sb.append(" and " + Global.DB_PREFIX + "user_cache.user_nature != 3 group by " + Global.DB_PREFIX + "user.user_id");
        Query query = em.createNativeQuery(sb.toString(), User.class);
        int pageNumber = model.getPage();
        int pageSize = model.getRows();
        Page page = new Page(query.getResultList().size(), pageNumber, pageSize);
        query.setFirstResult((pageNumber - 1) * pageSize);
        query.setMaxResults(pageSize);
        List<User> list = query.getResultList();
        pageDataList.setList(list);
        pageDataList.setPage(page);
        return pageDataList;
    }

    @Override
    public PageDataList<UserModel> userAttestationList(int pageNumber, int pageSize, UserModel model) {
        QueryParam param = QueryParam.getInstance().addPage(pageNumber, pageSize);
        param.addParam("userAttestation.user.userId", Operators.PROPERTY_EQ, "userId");
        param.addParam("userCache.user.userId", Operators.PROPERTY_EQ, "userId");
        if (!StringUtil.isBlank(model.getUserName())) {
            param.addParam("userName", Operators.LIKE, model.getUserName().trim());
        }
        if (!StringUtil.isBlank(model.getRealName())) {
            param.addParam("realName", Operators.LIKE, model.getRealName().trim());
        }
        PageDataList<User> pageDataList = this.findPageList(param);
        PageDataList<UserModel> pageDataList_ = new PageDataList<UserModel>();
        List<UserModel> list = new ArrayList<UserModel>();
        pageDataList_.setPage(pageDataList.getPage());
        if (pageDataList.getList().size() > 0) {
            for (int i = 0; i < pageDataList.getList().size(); i++) {
                User user = (User) pageDataList.getList().get(i);
                UserModel userModel = UserModel.instance(user);
                list.add(userModel);
            }
        }
        pageDataList_.setList(list);
        return pageDataList_;
    }

    @Override
    public List<User> getVouchFirms() {
        StringBuffer sb = new StringBuffer();
        sb.append("SELECT t1.* FROM " + Global.DB_PREFIX + "user t1," + Global.DB_PREFIX + "user_cache t2 WHERE t1.user_id = t2.user_id AND t2.user_nature = :user_nature AND status = :status");
        Query query = em.createNativeQuery(sb.toString(), User.class);
        query.setParameter("user_nature", UserNature.CORPORATE_JURIDICAL);
        query.setParameter("status", UserCacheStatus.UNLOCK);
        return query.getResultList();
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<User> getHasBonusUser() {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT * FROM " + Global.DB_PREFIX + "user u JOIN " + Global.DB_PREFIX + "account a ON a.user_id = u.user_id WHERE a.use_money > 0 AND u.tpp_user_cust_id != '' ");
        Query query = em.createNativeQuery(sb.toString(), User.class);
        return query.getResultList();
    }

    @Cacheable(value = "user", key = "'tppCustId_'+#tppCustId")
    @Override
    public User findByTppCustId(String tppCustId) {
        User user = findObjByProperty("tppUserCustId", tppCustId);
        return user;
    }

    @Override
    public List<UserModel> userListForExcel(int start, int itemCount, UserModel model) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT u.user_id userId, u.user_name userName, u.real_name realName, u.email email, u.mobile_phone mobilePhone, u.card_id cardId, i.real_name_status realNameStatus, c. STATUS, u.add_time addTime FROM `" + Global.DB_PREFIX + "user` u LEFT JOIN " + Global.DB_PREFIX + "user_identify i ON u.user_id = i.user_id LEFT JOIN " + Global.DB_PREFIX + "user_cache c ON u.user_id = c.user_id where 1 = 1 ");

        if (model.getStatus() != 99 && StringUtil.isBlank(model.getStatus())) {
            sb.append(" AND c.status = ").append(model.getStatus());
        }
        if (model.getUserIdentify() != null && model.getUserIdentify().getRealNameStatus() != -2) {
            sb.append(" AND i.real_name_status = ").append(model.getUserIdentify().getRealNameStatus());
        }

        if (!StringUtil.isBlank(model.getUserName())) {
            sb.append(" AND u.user_name LIKE '%").append(model.getUserName().trim()).append("%'");
        }
        if (!StringUtil.isBlank(model.getRealName())) {
            sb.append(" AND u.real_name LIKE '%").append(model.getRealName().trim()).append("%'");
        }

        if (!StringUtil.isBlank(model.getMobilePhone())) {
            sb.append(" AND u.mobile_phone LIKE '%").append(model.getRealName().trim()).append("%'");
        }
        if (StringUtil.isNotBlank(model.getStartTime())) {
            String startTime = "'" + model.getStartTime() + " 00:00:00'";
            sb.append(" AND u.add_time >").append(startTime);
        }
        if (StringUtil.isNotBlank(model.getEndTime())) {
            String endTime = "'" + model.getEndTime() + " 23:59:59'";
            sb.append(" AND u.add_time <").append(endTime);
        }
        sb.append(" LIMIT ").append(start);
        sb.append(" , ").append(itemCount);
        Query query = em.createNativeQuery(sb.toString());
        return query.getResultList();
    }

    @Override
    public int getCountByUserCondition(UserModel model) {
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT count(*) FROM `" + Global.DB_PREFIX + "user` u LEFT JOIN " + Global.DB_PREFIX + "user_identify i ON u.user_id = i.user_id LEFT JOIN " + Global.DB_PREFIX + "user_cache c ON u.user_id = c.user_id where 1 = 1 ");

        if (model.getStatus() != 99 && StringUtil.isBlank(model.getStatus())) {
            sb.append(" AND c.status = ").append(model.getStatus());
        }
        if (model.getUserIdentify() != null && model.getUserIdentify().getRealNameStatus() != -2) {
            sb.append(" AND i.real_name_status = ").append(model.getUserIdentify().getRealNameStatus());
        }

        if (!StringUtil.isBlank(model.getUserName())) {
            sb.append(" AND u.user_name LIKE '%").append(model.getUserName().trim()).append("%'");
        }
        if (!StringUtil.isBlank(model.getRealName())) {
            sb.append(" AND u.real_name LIKE '%").append(model.getRealName().trim()).append("%'");
        }

        if (!StringUtil.isBlank(model.getMobilePhone())) {
            sb.append(" AND u.mobile_phone LIKE '%").append(model.getRealName().trim()).append("%'");
        }
        if (StringUtil.isNotBlank(model.getStartTime())) {
            String startTime = "'" + model.getStartTime() + " 00:00:00'";
            sb.append(" AND u.add_time >").append(startTime);
        }
        if (StringUtil.isNotBlank(model.getEndTime())) {
            String endTime = "'" + model.getEndTime() + " 23:59:59'";
            sb.append(" AND u.add_time <").append(endTime);
        }
        Query query = em.createNativeQuery(sb.toString());
        return Integer.parseInt(query.getSingleResult() + "");
    }

    @Override
    public User getUserByMobilePhone(String mobilePhone) {
        String sql = "from User where mobilePhone = :mobilePhone";
        Query query = em.createQuery(sql);
        query.setParameter("mobilePhone", mobilePhone);
        List<User> list = query.getResultList();
        if (list != null && list.size() > 0) {
            return list.get(0);
        } else {
            return null;
        }
    }

    @Override
    public int getRegisterCountByDate(String date) {
        String dateFormat = "%Y-%m-%d";
        if (date.length() == 7) {
            dateFormat = "%Y-%m";
        }
        String sql = "select COUNT(user_id) from " + Global.DB_PREFIX+"user where date_format(add_time,'" + dateFormat + "') = ?  ";
        Query query = em.createNativeQuery(sql).setParameter(1, date);
        Object obj = query.getSingleResult();
        int count = 0;
        if (obj != null) {
            count = Integer.parseInt(((obj.toString())));
        }
        return count;
    }

}
